﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Diagnostics;
using System.IO;
using System.Data;
using System.Data.SqlClient;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.HSSF.Util;
using NPOI.POIFS.FileSystem;
using NPOI.HPSF;

public partial class UC_ucNH_TH : System.Web.UI.UserControl
{
    SqlParameter[] p;
    clsXuLy ex = new clsXuLy();
    NpoiExport cl = new NpoiExport();
    string frmName = "frmBaoCaoNhapHang";
    static int f_SuDung = 0, f_XemTatCa = 0, f_InBaoCao = 0, f_Khoa = 0;
    public string Header = System.Configuration.ConfigurationSettings.AppSettings["Header"].ToString();
    static int PageNumber = 10;
    static int CurrentPage = 1;
    static double klmuaVAT = 0, klmuaKVAT = 0, klthucte = 0, tlhang = 0, klsauquidoi = 0, giavlvat = 0, giavlkhongvat = 0, ttvlcovat = 0, ttvlkvat = 0, cvccovat = 0, cvckvt = 0, ttcvccovat = 0, ttcvckvat = 0, tt1m3covat = 0, tt1m3kvat = 0, tt1chuyencovat = 0, tt1chuyenkvat = 0;
    static string ncc = "";
    static System.Data.DataTable dt;
    System.Data.DataTable dtPrint;
    protected void Page_Load(object sender, EventArgs e)
    {
        if (string.IsNullOrEmpty(Convert.ToString(Session["user"])) || string.IsNullOrEmpty(Convert.ToString(Session["IDUser"])))
            Response.Redirect("Default.aspx");
        if (!IsPostBack)
        {
            string idnguoidung = Session["IDUser"].ToString();
            string sql = "select SuDung,Them,Sua,Xoa,InBaoCao,XemTatCa,Khoa from XDAPhanQuyen where IDnguoidung='" + idnguoidung + "' and tag='" + frmName + "'";

            System.Data.DataTable dt2 = ex.GetData_Text(sql);
            if (dt2.Rows.Count > 0)
            {
                f_SuDung = int.Parse(dt2.Rows[0]["SuDung"].ToString());
                f_InBaoCao = int.Parse(dt2.Rows[0]["InBaoCao"].ToString());
                f_XemTatCa = int.Parse(dt2.Rows[0]["XemTatCa"].ToString());
                f_Khoa = int.Parse(dt2.Rows[0]["Khoa"].ToString());
            }

            CurrentPage = 1;
            for (int i = 1; i <= 12; i++)
            {
                dlThang.Items.Add(new ListItem("Tháng " + i.ToString(), i.ToString()));
            }
            dlThang.Items.Insert(0, new ListItem("Theo thời gian", ""));
            dlThang.SelectedIndex = 0;

            int year1 = DateTime.Now.Year - 3;
            int year2 = DateTime.Now.Year;
            for (int i = year2; i >= year1; i--)
            {
                dlNam.Items.Add(new ListItem("Năm " + i.ToString(), i.ToString()));
            }
            dlNam.SelectedValue = year2.ToString();

            txtTuNgay.Text = DateTime.Now.ToString("dd/MM/yyyy");
            txtDenNgay.Text = DateTime.Now.ToString("dd/MM/yyyy");
            dlNam.Visible = false;

            LoadNhaCungCap(dlBenBan, "--Chọn bên bán--");
            LoadLoaiVatLieu(dlLoaiVLLoc);
            LoadTramTron(dlTramTron);
            //lbTrangThai.Visible = false;
            //btnKhoa.Visible = false;
        }
    }
    protected void btnPrint_Click(object sender, EventArgs e)
    {
        if (f_InBaoCao.Equals(1))
        {
            //tong hop
            if (dlBenBan.SelectedIndex.Equals(0) && dlLoaiVLLoc.SelectedIndex.Equals(0) && dlTramTron.SelectedIndex.Equals(0))
            {
                SqlParameter[] p1 = new SqlParameter[2];
                p1[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
                p1[0].SqlDbType = SqlDbType.SmallDateTime;
                p1[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
                p1[1].SqlDbType = SqlDbType.SmallDateTime;

                dtPrint = ex.GetData("sp_BCNH_TH_TongHop_Print", p1);
            }
            else
                //ben ban
                if (dlBenBan.SelectedIndex > 0 && dlLoaiVLLoc.SelectedIndex.Equals(0) && dlTramTron.SelectedIndex.Equals(0))
                {
                    SqlParameter[] p1 = new SqlParameter[3];
                    p1[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
                    p1[0].SqlDbType = SqlDbType.SmallDateTime;
                    p1[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
                    p1[1].SqlDbType = SqlDbType.SmallDateTime;
                    p1[2] = new SqlParameter("@BenBan", ex.GetGuid(dlBenBan.SelectedValue));
                    p1[2].SqlDbType = SqlDbType.UniqueIdentifier;

                    dtPrint = ex.GetData("sp_BCNH_TH_ByBenBan_Print", p1);
                }
                else
                    //ben ban - loai vl
                    if (dlBenBan.SelectedIndex > 0 && dlLoaiVLLoc.SelectedIndex > 0 && dlTramTron.SelectedIndex.Equals(0))
                    {
                        SqlParameter[] p1 = new SqlParameter[4];
                        p1[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
                        p1[0].SqlDbType = SqlDbType.SmallDateTime;
                        p1[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
                        p1[1].SqlDbType = SqlDbType.SmallDateTime;
                        p1[2] = new SqlParameter("@LoaiVL", ex.GetGuid(dlLoaiVLLoc.SelectedValue));
                        p1[2].SqlDbType = SqlDbType.UniqueIdentifier;
                        p1[3] = new SqlParameter("@BenBan", ex.GetGuid(dlBenBan.SelectedValue));
                        p1[3].SqlDbType = SqlDbType.UniqueIdentifier;

                        dtPrint = ex.GetData("sp_BCNH_TH_ByBenBanLoaiVL_Print", p1);
                    }
            try
            {
                PrintNPOI(dtPrint);
            }
            catch (Exception ax)
            {
                gstGetMess(ax.Message, "");
            }
        }
        else gstGetMess("Bạn không có quyền in báo cáo này", "");
    }
    protected void GV_RowCreated(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.Header)
        {
            GridView HeaderGrid = (GridView)sender;
            GridViewRow HeaderGridRow = new GridViewRow(0, 0, DataControlRowType.Header, DataControlRowState.Insert);
            HeaderGridRow.Height = 20;
            HeaderGridRow.HorizontalAlign = HorizontalAlign.Center;
            TableCell HeaderCell = new TableCell();

            HeaderGridRow.Cells.Add(HeaderCell);
            HeaderCell = new TableCell();
            HeaderCell.Text = "";
            HeaderCell.ColumnSpan = 1;

            HeaderGridRow.Cells.Add(HeaderCell);
            HeaderCell = new TableCell();
            HeaderCell.Text = "";
            HeaderCell.ColumnSpan = 1;
            HeaderGridRow.Cells.Add(HeaderCell);
            HeaderCell = new TableCell();
            HeaderCell.Text = "";
            HeaderCell.ColumnSpan = 1;
            HeaderGridRow.Cells.Add(HeaderCell);

            HeaderCell = new TableCell();
            HeaderCell.Text = "";
            HeaderCell.ColumnSpan = 1;
            HeaderGridRow.Cells.Add(HeaderCell);
            HeaderCell = new TableCell();
            HeaderCell.Text = "";
            HeaderCell.ColumnSpan = 1;
            HeaderGridRow.Cells.Add(HeaderCell);


            HeaderCell = new TableCell();
            HeaderCell.Text = "KL mua";
            HeaderCell.ColumnSpan = 2;
            HeaderGridRow.Cells.Add(HeaderCell);

            HeaderCell = new TableCell();
            HeaderCell.Text = "";
            HeaderCell.ColumnSpan = 1;
            HeaderGridRow.Cells.Add(HeaderCell);

            HeaderCell = new TableCell();
            HeaderCell.Text = "";
            HeaderCell.ColumnSpan = 1;
            HeaderGridRow.Cells.Add(HeaderCell);

            HeaderCell = new TableCell();
            HeaderCell.Text = "";
            HeaderCell.ColumnSpan = 1;
            HeaderGridRow.Cells.Add(HeaderCell);


            HeaderCell = new TableCell();
            HeaderCell.Text = "Giá vật liệu";
            HeaderCell.ColumnSpan = 2;
            HeaderGridRow.Cells.Add(HeaderCell);

            HeaderCell = new TableCell();
            HeaderCell.Text = "Thành tiền VL";
            HeaderCell.ColumnSpan = 2;
            HeaderGridRow.Cells.Add(HeaderCell);

            HeaderCell = new TableCell();
            HeaderCell.Text = "Giá cước vận chuyển";
            HeaderCell.ColumnSpan = 2;
            HeaderGridRow.Cells.Add(HeaderCell);

            HeaderCell = new TableCell();
            HeaderCell.Text = "Thành tiền cước VC";
            HeaderCell.ColumnSpan = 2;
            HeaderGridRow.Cells.Add(HeaderCell);

            HeaderCell = new TableCell();
            HeaderCell.Text = "Thành tiền /1m3";
            HeaderCell.ColumnSpan = 2;
            HeaderGridRow.Cells.Add(HeaderCell);

            HeaderCell = new TableCell();
            HeaderCell.Text = "Thành tiền /1chuyến";
            HeaderCell.ColumnSpan = 2;
            HeaderGridRow.Cells.Add(HeaderCell);



            GV.Controls[0].Controls.AddAt(0, HeaderGridRow);
        }
    }
    void PrintNPOI(System.Data.DataTable dt)
    {
        var workbook = new HSSFWorkbook();
        string congno = "";
        if (dt.Rows.Count > 0)
        {
            string sheetname = dlBenBan.SelectedItem.Text.Length > 30 ? dlBenBan.SelectedItem.Text.Substring(0, 30).Replace("*", "x") : dlBenBan.SelectedItem.Text.Replace("*", "x");
            var sheet = workbook.CreateSheet(dlBenBan.SelectedIndex > 0 ? sheetname : "Bảng tổng hợp");

            #region CSS
            var headerLabelCellStyle = workbook.CreateCellStyle();
            headerLabelCellStyle.Alignment = HorizontalAlignment.LEFT;
            headerLabelCellStyle.BorderBottom = CellBorderType.THIN;
            headerLabelCellStyle.BorderLeft = CellBorderType.THIN;
            headerLabelCellStyle.BorderRight = CellBorderType.THIN;
            headerLabelCellStyle.BorderTop = CellBorderType.THIN;

            var headerLabelFont = workbook.CreateFont();
            headerLabelFont.FontName = "Times New Roman";

            headerLabelFont.FontHeightInPoints = 11;
            headerLabelFont.Color = HSSFColor.BLACK.index;
            headerLabelCellStyle.SetFont(headerLabelFont);

            //--------------------------

            var hsRight = workbook.CreateCellStyle();
            hsRight.Alignment = HorizontalAlignment.RIGHT;
            hsRight.BorderBottom = CellBorderType.THIN;
            hsRight.BorderLeft = CellBorderType.THIN;
            hsRight.BorderRight = CellBorderType.THIN;
            hsRight.BorderTop = CellBorderType.THIN;

            hsRight.SetFont(headerLabelFont);
            //-------------------
            var hsCenter = workbook.CreateCellStyle();
            hsCenter.Alignment = HorizontalAlignment.CENTER;
            hsCenter.BorderBottom = CellBorderType.THIN;
            hsCenter.BorderLeft = CellBorderType.THIN;
            hsCenter.BorderRight = CellBorderType.THIN;
            hsCenter.BorderTop = CellBorderType.THIN;

            hsCenter.SetFont(headerLabelFont);
            //-----------------------

            var hs1 = workbook.CreateCellStyle();
            hs1.Alignment = HorizontalAlignment.LEFT;

            var hsb = workbook.CreateFont();
            hsb.Boldweight = (short)FontBoldWeight.BOLD;
            hsb.FontName = "Times New Roman";

            hsb.FontHeightInPoints = 11;
            hsb.Color = HSSFColor.BLACK.index;
            hs1.SetFont(hsb);

            //-------------
            var hs2 = workbook.CreateCellStyle();
            hs2.Alignment = HorizontalAlignment.CENTER;

            var hsb2 = workbook.CreateFont();
            hsb2.Boldweight = (short)FontBoldWeight.BOLD;
            hsb2.FontName = "Times New Roman";

            hsb2.FontHeightInPoints = 18;
            hsb2.Color = HSSFColor.BLACK.index;
            hs2.SetFont(hsb2);
            //------------------------------
            var hs3 = workbook.CreateCellStyle();
            hs3.Alignment = HorizontalAlignment.CENTER;
            hs3.BorderBottom = CellBorderType.THIN;
            var hsb3 = workbook.CreateFont();
            hsb3.Boldweight = (short)FontBoldWeight.BOLD;
            hsb3.FontName = "Times New Roman";

            hsb3.FontHeightInPoints = 14;
            hsb3.Color = HSSFColor.BLACK.index;
            hs3.SetFont(hsb3);

            //----------------------------
            var hs4 = workbook.CreateCellStyle();
            hs4.Alignment = HorizontalAlignment.CENTER;
            hs4.BorderBottom = CellBorderType.THIN;
            hs4.BorderLeft = CellBorderType.THIN;
            hs4.BorderRight = CellBorderType.THIN;
            hs4.BorderTop = CellBorderType.THIN;

            var hsb4 = workbook.CreateFont();
            hsb4.Boldweight = (short)FontBoldWeight.BOLD;
            hsb4.FontName = "Times New Roman";

            hsb4.FontHeightInPoints = 11;
            hsb4.Color = HSSFColor.BLACK.index;
            hs4.SetFont(hsb4);

            #endregion

            //Start header-----------------------------------------------------------------------
            #region Header
            //tao hnag dau tien
            var rowIndex = 0;

            //cong ty
            var row = sheet.CreateRow(rowIndex);
            Cell r1c1 = row.CreateCell(0);
            r1c1.SetCellValue("Công ty cổ phần Xây Dựng Đức Anh");
            r1c1.CellStyle = hs1;
            r1c1.Row.Height = 400;

            CellRangeAddress cra = new CellRangeAddress(0, 0, 0, 4);
            sheet.AddMergedRegion(cra);

            //chi nhanh
            rowIndex++;
            row = sheet.CreateRow(rowIndex);
            r1c1 = row.CreateCell(0);
            r1c1.SetCellValue("Chi nhánh Trạm trộn Lâm Thao");
            r1c1.CellStyle = hs1;
            r1c1.Row.Height = 400;

            cra = new CellRangeAddress(1, 1, 0, 4);
            sheet.AddMergedRegion(cra);

            //tieu de bao cao
            string ngaythang = dlThang.SelectedIndex > 0 ? dlThang.SelectedItem.Text.ToUpper() + " " + dlNam.SelectedItem.Text.ToUpper() : "TỪ " + txtTuNgay.Text + " ĐẾN " + txtDenNgay.Text;
            rowIndex++;
            row = sheet.CreateRow(rowIndex);
            r1c1 = row.CreateCell(0);
            r1c1.SetCellValue("BÁO CÁO SẢN LƯỢNG NHẬP " + ngaythang);
            r1c1.CellStyle = hs2;
            r1c1.Row.Height = 500;

            cra = new CellRangeAddress(2, 2, 0, 12);
            sheet.AddMergedRegion(cra);

            //nha cung cap
            rowIndex++;
            row = sheet.CreateRow(rowIndex);
            r1c1 = row.CreateCell(0);
            r1c1.SetCellValue(dlBenBan.SelectedIndex > 0 ? "Nhà cung cấp: " + dlBenBan.SelectedItem.Text : "");
            r1c1.CellStyle = hs3;
            r1c1.Row.Height = 500;

            cra = new CellRangeAddress(3, 3, 0, 12);
            sheet.AddMergedRegion(cra);

            //freeze panes
            sheet.CreateFreezePane(0, 6);
            #endregion
            //End header-----------------------------------------------------------------------

            #region Header1-2
            rowIndex++;
            //header1
            row = sheet.CreateRow(rowIndex);
            string[] header1 = { "Khối lượng mua", "Giá vật liệu", "TT vật liệu", "Giá cước VC", "TT Cước VC", "TT hàng/1M3", "TT hàng/1 chuyến" };
            int[] me = { 7, 12, 14, 16, 18, 20, 22 };

            //tao header1
            for (int m = 0; m <= 6; m++)
            {

                r1c1 = row.CreateCell(me[m]);
                r1c1.SetCellValue(header1[m]);
                r1c1.CellStyle = hs4;
                r1c1.Row.Height = 400;

                cra = new CellRangeAddress(4, 4, me[m], me[m] + 1);
                sheet.AddMergedRegion(cra);

            }

            //fix lai border
            for (int h = 0; h <= 23; h++)
            {
                if (h != 7 && h != 12 && h != 14 && h != 16 && h != 18 && h != 20 && h != 22)
                {
                    r1c1 = row.CreateCell(h);
                    r1c1.CellStyle = hs4;
                    r1c1.Row.Height = 400;
                }
            }

            //header2
            rowIndex++;
            row = sheet.CreateRow(rowIndex);
            string[] header = { "Số phiếu", "Ngày nhập", "Loại vật liệu", "Bên bán", "Bên chạy cước", "Biển số xe", "Có VAT", "K.VAT", "KL thực tế", "TL hàng", "TL sau qui đổi", "Có VAT", "K.VAT", "Có VAT", "K.VAT", "Có VAT", "K.VAT", "Có VAT", "K.VAT", "Có VAT", "K.VAT", "Có VAT", "K.VAT" };

            var cell = row.CreateCell(0);
            cell.SetCellValue("STT");
            cell.CellStyle = hs4;
            cell.Row.Height = 400;


            //tao tieu de cot
            for (int j = 1; j <= header.Length; j++)
            {
                cell = row.CreateCell(j);
                cell.SetCellValue(header[j - 1].ToString());
                cell.CellStyle = hs4;
                cell.Row.Height = 500;
            }
            #endregion

            //ghi du lieu tung dong
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                rowIndex++;
                row = sheet.CreateRow(rowIndex);

                cell = row.CreateCell(0);
                cell.SetCellValue((i + 1).ToString());
                cell.CellStyle = hsCenter;
                cell.Row.Height = 500;


                for (int j = 1; j <= dt.Columns.Count; j++)
                {
                    cell = row.CreateCell(j);
                    if (j >= 7)
                    {
                        if (!dt.Rows[i][j - 1].ToString().Trim().Equals(""))
                        {
                            cell.SetCellType(CellType.NUMERIC);
                            cell.SetCellValue(double.Parse(dt.Rows[i][j - 1].ToString()));
                        }
                        cell.CellStyle = hsRight;
                    }
                    else
                    {
                        cell.SetCellValue(dt.Rows[i][j - 1].ToString());

                        if (j <= 2)
                            cell.CellStyle = hsCenter;
                        else
                            cell.CellStyle = headerLabelCellStyle;
                    }
                    cell.Row.Height = 500;
                }

            }

            #region Footer
            //-----------Footer
            string[] footer = { "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X" };
            rowIndex++;
            row = sheet.CreateRow(rowIndex);

            cell = row.CreateCell(3);
            cell.SetCellValue("Tổng cộng");
            cell.Row.Height = 500;
            cell.CellStyle = hs3;

            for (int k = 0; k <= 16; k++)
            {
                if (k != 5 && k != 6 && k != 9 && k != 10)
                {
                    cell = row.CreateCell(k + 7);
                    cell.CellFormula = "Sum(" + footer[k] + "7:" + footer[k] + rowIndex.ToString() + ")";
                    cell.Row.Height = 500;
                    cell.CellStyle = hs4;
                }
            }
            //fix lai border
            for (int h = 0; h <= 23; h++)
            {
                if (h != 3 && (h < 7 || (h > 11 && h < 14) || (h >= 16 && h < 18)))
                {
                    r1c1 = row.CreateCell(h);
                    r1c1.CellStyle = hs4;
                    r1c1.Row.Height = 400;
                }
            }
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                sheet.AutoSizeColumn(i);

            }
            #endregion

            //end sheet tong hop--------------------------

            //--Start sheet con, neu ko chon ben ban thi in tung ben ban mot
            if (dlBenBan.SelectedIndex.Equals(0))
            {
                #region TungBenBan
                int STT = 0;
                //ghi sheet con------------------------------------------
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    STT++;
                    #region Start
                    if (i > 0)
                    {
                        ncc = dt.Rows[i][3].ToString();
                        if (!ncc.Equals(dt.Rows[i - 1][3].ToString()))
                        {
                            STT = 0;
                            //-----------Footer
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);

                            cell = row.CreateCell(3);
                            cell.SetCellValue("Tổng cộng");
                            cell.Row.Height = 500;
                            cell.CellStyle = hs3;

                            for (int k = 0; k <= 16; k++)
                            {
                                if (k != 5 && k != 6 && k != 9 && k != 10)
                                {
                                    cell = row.CreateCell(k + 7);
                                    cell.CellFormula = "Sum(" + footer[k] + "7:" + footer[k] + rowIndex.ToString() + ")";
                                    cell.Row.Height = 500;
                                    cell.CellStyle = hs4;
                                }
                            }
                            //fix lai border
                            for (int h = 0; h <= 23; h++)
                            {
                                if (h != 3 && (h < 7 || (h > 11 && h < 14) || (h >= 16 && h < 18)))
                                {
                                    r1c1 = row.CreateCell(h);
                                    r1c1.CellStyle = hs4;
                                    r1c1.Row.Height = 400;
                                }
                            }
                            for (int j = 0; j < dt.Columns.Count; j++)
                            {
                                sheet.AutoSizeColumn(j);
                            }

                            sheet = workbook.CreateSheet(ncc.Length > 30 ? ncc.Substring(0, 30) : ncc);
                            rowIndex = 0;

                            //Start header-----------------------------------------------------------------------
                            #region Header
                            //tao hnag dau tien
                            //cong ty
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("Công ty cổ phần Xây Dựng Đức Anh");
                            r1c1.CellStyle = hs1;
                            r1c1.Row.Height = 400;

                            cra = new CellRangeAddress(0, 0, 0, 4);
                            sheet.AddMergedRegion(cra);

                            //chi nhanh
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("Chi nhánh Trạm trộn Lâm Thao");
                            r1c1.CellStyle = hs1;
                            r1c1.Row.Height = 400;

                            cra = new CellRangeAddress(1, 1, 0, 4);
                            sheet.AddMergedRegion(cra);

                            //tieu de bao cao
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("BÁO CÁO SẢN LƯỢNG NHẬP");
                            r1c1.CellStyle = hs2;
                            r1c1.Row.Height = 500;

                            cra = new CellRangeAddress(2, 2, 0, 12);
                            sheet.AddMergedRegion(cra);

                            //nha cung cap
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("Nhà cung cấp: " + ncc);
                            r1c1.CellStyle = hs3;
                            r1c1.Row.Height = 500;

                            cra = new CellRangeAddress(3, 3, 0, 12);
                            sheet.AddMergedRegion(cra);

                            //freeze panes
                            sheet.CreateFreezePane(0, 6);
                            #endregion
                            //End header-----------------------------------------------------------------------

                            rowIndex++;

                            row = sheet.CreateRow(rowIndex);

                            //tao header1
                            for (int m = 0; m <= 6; m++)
                            {

                                r1c1 = row.CreateCell(me[m]);
                                r1c1.SetCellValue(header1[m]);
                                r1c1.CellStyle = hs4;
                                r1c1.Row.Height = 400;

                                cra = new CellRangeAddress(4, 4, me[m], me[m] + 1);
                                sheet.AddMergedRegion(cra);

                            }

                            //fix lai border
                            for (int h = 0; h <= 23; h++)
                            {
                                if (h != 7 && h != 12 && h != 14 && h != 16 && h != 18 && h != 20 && h != 22)
                                {
                                    r1c1 = row.CreateCell(h);
                                    r1c1.CellStyle = hs4;
                                    r1c1.Row.Height = 400;
                                }
                            }
                            //header2
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);

                            cell = row.CreateCell(0);
                            cell.SetCellValue("STT");
                            cell.CellStyle = hs4;
                            cell.Row.Height = 400;

                            for (int j = 1; j <= header.Length; j++)
                            {
                                //ghi cac cot
                                cell = row.CreateCell(j);
                                cell.SetCellValue(header[j - 1].ToString());
                                cell.CellStyle = hs4;
                                cell.Row.Height = 500;
                            }
                            STT++;
                        }
                    }
                    else
                    {

                        if (!ncc.Equals(dt.Rows[i][3].ToString()))
                        {
                            ncc = dt.Rows[i][3].ToString();
                            sheet = workbook.CreateSheet(ncc.Length > 30 ? ncc.Substring(0, 30) : ncc);
                            rowIndex = 0;

                            //Start header-----------------------------------------------------------------------
                            #region Header
                            //tao hnag dau tien
                            //cong ty
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("Công ty cổ phần Xây Dựng Đức Anh");
                            r1c1.CellStyle = hs1;
                            r1c1.Row.Height = 400;

                            cra = new CellRangeAddress(0, 0, 0, 4);
                            sheet.AddMergedRegion(cra);

                            //chi nhanh
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("Chi nhánh Trạm trộn Lâm Thao");
                            r1c1.CellStyle = hs1;
                            r1c1.Row.Height = 400;

                            cra = new CellRangeAddress(1, 1, 0, 4);
                            sheet.AddMergedRegion(cra);

                            //tieu de bao cao
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("BÁO CÁO SẢN LƯỢNG NHẬP");
                            r1c1.CellStyle = hs2;
                            r1c1.Row.Height = 500;

                            cra = new CellRangeAddress(2, 2, 0, 12);
                            sheet.AddMergedRegion(cra);

                            //nha cung cap
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("Nhà cung cấp: " + ncc);
                            r1c1.CellStyle = hs3;
                            r1c1.Row.Height = 500;

                            cra = new CellRangeAddress(3, 3, 0, 12);
                            sheet.AddMergedRegion(cra);

                            //freeze panes
                            sheet.CreateFreezePane(0, 5);
                            #endregion
                            //End header-----------------------------------------------------------------------

                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);

                            //tao header1
                            for (int m = 0; m <= 6; m++)
                            {

                                r1c1 = row.CreateCell(me[m]);
                                r1c1.SetCellValue(header1[m]);
                                r1c1.CellStyle = hs4;
                                r1c1.Row.Height = 400;

                                cra = new CellRangeAddress(4, 4, me[m], me[m] + 1);
                                sheet.AddMergedRegion(cra);

                            }

                            //fix lai border
                            for (int h = 0; h <= 23; h++)
                            {
                                if (h != 7 && h != 12 && h != 14 && h != 16 && h != 18 && h != 20 && h != 22)
                                {
                                    r1c1 = row.CreateCell(h);
                                    r1c1.CellStyle = hs4;
                                    r1c1.Row.Height = 400;
                                }
                            }
                            //header2
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);

                            cell = row.CreateCell(0);
                            cell.SetCellValue("STT");
                            cell.CellStyle = hs4;
                            cell.Row.Height = 400;

                            for (int j = 1; j <= header.Length; j++)
                            {
                                //ghi cac cot
                                cell = row.CreateCell(j);
                                cell.SetCellValue(header[j - 1].ToString());
                                cell.CellStyle = hs4;
                                cell.Row.Height = 500;
                            }
                        }
                    }
                    #endregion

                    rowIndex++;
                    row = sheet.CreateRow(rowIndex);

                    cell = row.CreateCell(0);
                    cell.SetCellValue(STT.ToString());
                    cell.CellStyle = hsCenter;
                    cell.Row.Height = 500;

                    for (int j = 1; j <= dt.Columns.Count; j++)
                    {
                        cell = row.CreateCell(j);

                        if (j >= 7)
                        {
                            if (!dt.Rows[i][j - 1].ToString().Trim().Equals(""))
                            {
                                cell.SetCellType(CellType.NUMERIC);
                                cell.SetCellValue(double.Parse(dt.Rows[i][j - 1].ToString()));
                            }
                            cell.CellStyle = hsRight;
                        }
                        else
                        {
                            cell.SetCellValue(dt.Rows[i][j - 1].ToString());

                            if (j <= 2)
                                cell.CellStyle = hsCenter;
                            else
                                cell.CellStyle = headerLabelCellStyle;
                        }
                        cell.Row.Height = 500;
                    }

                    if (i == dt.Rows.Count - 1)
                    {
                        //-----------Footer
                        rowIndex++;
                        row = sheet.CreateRow(rowIndex);

                        cell = row.CreateCell(3);
                        cell.SetCellValue("Tổng cộng");
                        cell.Row.Height = 500;
                        cell.CellStyle = hs3;

                        for (int k = 0; k <= 16; k++)
                        {
                            if (k != 5 && k != 6 && k != 9 && k != 10)
                            {
                                cell = row.CreateCell(k + 7);
                                cell.CellFormula = "Sum(" + footer[k] + "7:" + footer[k] + rowIndex.ToString() + ")";
                                cell.Row.Height = 500;
                                cell.CellStyle = hs4;
                            }
                        }
                        //fix lai border
                        for (int h = 0; h <= 23; h++)
                        {
                            if (h != 3 && (h < 7 || (h > 11 && h < 14) || (h >= 16 && h < 18)))
                            {
                                r1c1 = row.CreateCell(h);
                                r1c1.CellStyle = hs4;
                                r1c1.Row.Height = 400;
                            }
                        }

                        for (int j = 0; j < dt.Columns.Count; j++)
                        {
                            sheet.AutoSizeColumn(j);
                        }
                    }

                }
                #endregion
            }
            else
            {
                //khi chon nha cung cap thi in tung loai vl
                int STT = 0;

                double tong1 = 0, tong2 = 0, tong3 = 0, tong4 = 0;

                //ghi sheet con------------------------------------------
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    STT++;
                    #region Start
                    if (i > 0)
                    {
                        ncc = dt.Rows[i][2].ToString();
                        if (!ncc.Equals(dt.Rows[i - 1][2].ToString()))
                        {
                            STT = 0;
                            //-----------Footer
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);

                            cell = row.CreateCell(3);
                            cell.SetCellValue("Tổng cộng");
                            cell.Row.Height = 500;
                            cell.CellStyle = hs3;

                            for (int k = 0; k <= 8; k++)
                            {
                                if (k != 5 && k != 6)
                                {
                                    cell = row.CreateCell(k + 7);
                                    cell.CellFormula = "Sum(" + footer[k] + "7:" + footer[k] + rowIndex.ToString() + ")";
                                    cell.Row.Height = 500;
                                    cell.CellStyle = hs4;
                                }
                            }
                            //fix lai border
                            for (int h = 0; h <= 15; h++)
                            {
                                if (h != 3 && (h < 7 || (h > 11 && h < 14)))
                                {
                                    r1c1 = row.CreateCell(h);
                                    r1c1.CellStyle = hs4;
                                    r1c1.Row.Height = 400;
                                }
                            }
                            for (int j = 0; j < dt.Columns.Count - 8; j++)
                            {
                                sheet.AutoSizeColumn(j);
                            }
                            congno += tong1.ToString() + "-" + tong2.ToString() + "-" + tong3.ToString() + "-" + tong4.ToString() + "@";

                            congno += cl.EscapeSheetName(ncc) + "--";
                            tong4 = tong3 = tong2 = tong1 = 0;
                            sheet = workbook.CreateSheet(cl.EscapeSheetName(ncc));
                            rowIndex = 0;

                            //Start header-----------------------------------------------------------------------
                            #region Header
                            //tao hnag dau tien
                            //cong ty
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("Công ty cổ phần Xây Dựng Đức Anh");
                            r1c1.CellStyle = hs1;
                            r1c1.Row.Height = 400;

                            cra = new CellRangeAddress(0, 0, 0, 4);
                            sheet.AddMergedRegion(cra);

                            //chi nhanh
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("Chi nhánh Trạm trộn Lâm Thao");
                            r1c1.CellStyle = hs1;
                            r1c1.Row.Height = 400;

                            cra = new CellRangeAddress(1, 1, 0, 4);
                            sheet.AddMergedRegion(cra);

                            //tieu de bao cao
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("BÁO CÁO SẢN LƯỢNG NHẬP " + ngaythang);
                            r1c1.CellStyle = hs2;
                            r1c1.Row.Height = 500;

                            cra = new CellRangeAddress(2, 2, 0, 12);
                            sheet.AddMergedRegion(cra);

                            //nha cung cap
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("Nhà cung cấp: " + dlBenBan.SelectedItem.Text + "-" + ncc);
                            r1c1.CellStyle = hs3;
                            r1c1.Row.Height = 500;

                            cra = new CellRangeAddress(3, 3, 0, 12);
                            sheet.AddMergedRegion(cra);

                            //freeze panes
                            sheet.CreateFreezePane(0, 6);
                            #endregion
                            //End header-----------------------------------------------------------------------

                            rowIndex++;

                            row = sheet.CreateRow(rowIndex);

                            //tao header1
                            for (int m = 0; m <= 2; m++)
                            {

                                r1c1 = row.CreateCell(me[m]);
                                r1c1.SetCellValue(header1[m]);
                                r1c1.CellStyle = hs4;
                                r1c1.Row.Height = 400;

                                cra = new CellRangeAddress(4, 4, me[m], me[m] + 1);
                                sheet.AddMergedRegion(cra);

                            }

                            //fix lai border
                            for (int h = 0; h <= 15; h++)
                            {
                                if (h != 7 && h != 12 && h != 14)
                                {
                                    r1c1 = row.CreateCell(h);
                                    r1c1.CellStyle = hs4;
                                    r1c1.Row.Height = 400;
                                }
                            }
                            //header2
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);

                            cell = row.CreateCell(0);
                            cell.SetCellValue("STT");
                            cell.CellStyle = hs4;
                            cell.Row.Height = 400;

                            for (int j = 1; j <= header.Length - 8; j++)
                            {
                                //ghi cac cot
                                cell = row.CreateCell(j);
                                cell.SetCellValue(header[j - 1].ToString());
                                cell.CellStyle = hs4;
                                cell.Row.Height = 500;
                            }
                            STT++;
                        }
                    }
                    else
                    {

                        if (!ncc.Equals(dt.Rows[i][2].ToString()))
                        {
                            congno = "";
                            ncc = dt.Rows[i][2].ToString();
                            sheet = workbook.CreateSheet(cl.EscapeSheetName(ncc));
                            congno += cl.EscapeSheetName(ncc) + "--";
                            rowIndex = 0;

                            //Start header-----------------------------------------------------------------------
                            #region Header
                            //tao hnag dau tien
                            //cong ty
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("Công ty cổ phần Xây Dựng Đức Anh");
                            r1c1.CellStyle = hs1;
                            r1c1.Row.Height = 400;

                            cra = new CellRangeAddress(0, 0, 0, 4);
                            sheet.AddMergedRegion(cra);

                            //chi nhanh
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("Chi nhánh Trạm trộn Lâm Thao");
                            r1c1.CellStyle = hs1;
                            r1c1.Row.Height = 400;

                            cra = new CellRangeAddress(1, 1, 0, 4);
                            sheet.AddMergedRegion(cra);

                            //tieu de bao cao
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("BÁO CÁO SẢN LƯỢNG NHẬP " + ngaythang);
                            r1c1.CellStyle = hs2;
                            r1c1.Row.Height = 500;

                            cra = new CellRangeAddress(2, 2, 0, 12);
                            sheet.AddMergedRegion(cra);

                            //nha cung cap
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("Nhà cung cấp: " + dlBenBan.SelectedItem.Text + "-" + ncc);
                            r1c1.CellStyle = hs3;
                            r1c1.Row.Height = 500;

                            cra = new CellRangeAddress(3, 3, 0, 12);
                            sheet.AddMergedRegion(cra);

                            //freeze panes
                            sheet.CreateFreezePane(0, 6);
                            #endregion
                            //End header-----------------------------------------------------------------------

                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);

                            //tao header1
                            for (int m = 0; m <= 2; m++)
                            {

                                r1c1 = row.CreateCell(me[m]);
                                r1c1.SetCellValue(header1[m]);
                                r1c1.CellStyle = hs4;
                                r1c1.Row.Height = 400;

                                cra = new CellRangeAddress(4, 4, me[m], me[m] + 1);
                                sheet.AddMergedRegion(cra);

                            }

                            //fix lai border
                            for (int h = 0; h <= 15; h++)
                            {
                                if (h != 7 && h != 12 && h != 14)
                                {
                                    r1c1 = row.CreateCell(h);
                                    r1c1.CellStyle = hs4;
                                    r1c1.Row.Height = 400;
                                }
                            }
                            //header2
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);

                            cell = row.CreateCell(0);
                            cell.SetCellValue("STT");
                            cell.CellStyle = hs4;
                            cell.Row.Height = 400;

                            for (int j = 1; j <= header.Length - 8; j++)
                            {
                                //ghi cac cot
                                cell = row.CreateCell(j);
                                cell.SetCellValue(header[j - 1].ToString());
                                cell.CellStyle = hs4;
                                cell.Row.Height = 500;
                            }
                        }
                    }
                    #endregion

                    rowIndex++;
                    row = sheet.CreateRow(rowIndex);

                    cell = row.CreateCell(0);
                    cell.SetCellValue(STT.ToString());
                    cell.CellStyle = hsCenter;
                    cell.Row.Height = 500;

                    for (int j = 1; j <= dt.Columns.Count - 8; j++)
                    {
                        cell = row.CreateCell(j);

                        if (j >= 7)
                        {
                            if (!dt.Rows[i][j - 1].ToString().Trim().Equals(""))
                            {
                                cell.SetCellType(CellType.NUMERIC);
                                cell.SetCellValue(double.Parse(dt.Rows[i][j - 1].ToString()));
                            }
                            cell.CellStyle = hsRight;
                        }
                        else
                        {
                            cell.SetCellValue(dt.Rows[i][j - 1].ToString());

                            if (j <= 2)
                                cell.CellStyle = hsCenter;
                            else
                                cell.CellStyle = headerLabelCellStyle;
                        }
                        cell.Row.Height = 500;
                    }

                    tong1 += double.Parse(dt.Rows[i][6].ToString().Trim().Equals("") ? "0" : dt.Rows[i][6].ToString());
                    tong2 += double.Parse(dt.Rows[i][7].ToString().Trim().Equals("") ? "0" : dt.Rows[i][7].ToString());
                    tong3 += double.Parse(dt.Rows[i][13].ToString().Trim().Equals("") ? "0" : dt.Rows[i][13].ToString());
                    tong4 += double.Parse(dt.Rows[i][14].ToString().Trim().Equals("") ? "0" : dt.Rows[i][14].ToString());


                    if (i == dt.Rows.Count - 1)
                    {
                        //-----------Footer
                        rowIndex++;
                        row = sheet.CreateRow(rowIndex);

                        cell = row.CreateCell(3);
                        cell.SetCellValue("Tổng cộng");
                        cell.Row.Height = 500;
                        cell.CellStyle = hs3;

                        for (int k = 0; k <= 8; k++)
                        {
                            if (k != 5 && k != 6)
                            {
                                cell = row.CreateCell(k + 7);
                                cell.CellFormula = "Sum(" + footer[k] + "7:" + footer[k] + rowIndex.ToString() + ")";
                                cell.Row.Height = 500;
                                cell.CellStyle = hs4;
                            }
                        }
                        //fix lai border
                        for (int h = 0; h <= 15; h++)
                        {
                            if (h != 3 && (h < 7 || (h > 11 && h < 14)))
                            {
                                r1c1 = row.CreateCell(h);
                                r1c1.CellStyle = hs4;
                                r1c1.Row.Height = 400;
                            }
                        }

                        for (int j = 0; j < dt.Columns.Count; j++)
                        {
                            sheet.AutoSizeColumn(j);
                        }
                        congno += tong1.ToString() + "-" + tong2.ToString() + "-" + tong3.ToString() + "-" + tong4.ToString() + "@";
                    }

                }
                //Load tong cong no

                sheet = workbook.CreateSheet("Tổng hợp công nợ");
                #region Header
                rowIndex = 0;
                //cong ty
                row = sheet.CreateRow(rowIndex);
                r1c1 = row.CreateCell(0);
                r1c1.SetCellValue("Công ty cổ phần Xây Dựng Đức Anh");
                r1c1.CellStyle = hs1;
                r1c1.Row.Height = 400;

                cra = new CellRangeAddress(0, 0, 0, 4);
                sheet.AddMergedRegion(cra);

                //chi nhanh
                rowIndex++;
                row = sheet.CreateRow(rowIndex);
                r1c1 = row.CreateCell(0);
                r1c1.SetCellValue("Chi nhánh Trạm trộn Lâm Thao");
                r1c1.CellStyle = hs1;
                r1c1.Row.Height = 400;

                cra = new CellRangeAddress(1, 1, 0, 4);
                sheet.AddMergedRegion(cra);

                //tieu de bao cao
                rowIndex++;
                row = sheet.CreateRow(rowIndex);
                r1c1 = row.CreateCell(0);
                r1c1.SetCellValue("TỔNG HỢP CÔNG NỢ " + ngaythang);
                r1c1.CellStyle = hs2;
                r1c1.Row.Height = 500;

                cra = new CellRangeAddress(2, 2, 0, 9);
                sheet.AddMergedRegion(cra);

                //nha cung cap
                rowIndex++;
                row = sheet.CreateRow(rowIndex);
                r1c1 = row.CreateCell(0);
                r1c1.SetCellValue("Nhà cung cấp: " + dlBenBan.SelectedItem.Text + "-" + ncc);
                r1c1.CellStyle = hs3;
                r1c1.Row.Height = 500;

                cra = new CellRangeAddress(3, 3, 0, 9);
                sheet.AddMergedRegion(cra);

                //freeze panes
                sheet.CreateFreezePane(0, 6);

                rowIndex++;
                row = sheet.CreateRow(rowIndex);
                r1c1 = row.CreateCell(3);
                r1c1.SetCellValue("Khối lượng mua");
                r1c1.CellStyle = hs4;
                r1c1.Row.Height = 500;
                cra = new CellRangeAddress(4, 4, 3, 4);
                sheet.AddMergedRegion(cra);

                r1c1 = row.CreateCell(5);
                r1c1.SetCellValue("Đơn giá");
                r1c1.CellStyle = hs4;
                r1c1.Row.Height = 500;
                cra = new CellRangeAddress(4, 4, 5, 6);
                sheet.AddMergedRegion(cra);

                r1c1 = row.CreateCell(7);
                r1c1.SetCellValue("Thành tiền");
                r1c1.CellStyle = hs4;
                r1c1.Row.Height = 500;
                cra = new CellRangeAddress(4, 4, 7, 8);
                sheet.AddMergedRegion(cra);


                //fix lai border
                for (int h = 0; h <= 9; h++)
                {
                    if (h != 3 && h != 5 && h != 7)
                    {
                        r1c1 = row.CreateCell(h);
                        r1c1.CellStyle = hs4;
                        r1c1.Row.Height = 400;
                    }
                }


                rowIndex++;
                row = sheet.CreateRow(rowIndex);

                string[] lm = { "STT", "Loại vật liệu", "Đơn vị tính", "Có VAT", "K.VAT", "Có VAT", "K.VAT", "Có VAT", "K.VAT", "Tổng tiền" };
                for (int lk = 0; lk < lm.Length; lk++)
                {
                    r1c1 = row.CreateCell(lk);
                    r1c1.SetCellValue(lm[lk].ToString());
                    r1c1.CellStyle = hs4;
                    r1c1.Row.Height = 500;
                }
                #endregion

                string[] mk = congno.Substring(0, congno.Length - 1).Split('@');

                string[] mkis = { "B", "C", "D", "E", "F", "G" };


                for (int m = 0; m < mk.Length; m++)
                {
                    rowIndex++;
                    row = sheet.CreateRow(rowIndex);

                    cell = row.CreateCell(0);
                    cell.SetCellValue((m + 1).ToString());
                    cell.CellStyle = hsCenter;

                    for (int h = 0; h < mkis.Length; h++)
                    {
                        if (h >= 4)
                            cell = row.CreateCell(h + 3);
                        else
                            cell = row.CreateCell(h + 1);
                        if (h >= 3 && h <= 6)
                        {
                            if (!mk[m].Split('-')[h].ToString().Trim().Equals(""))
                            {
                                cell.SetCellType(CellType.NUMERIC);
                                cell.SetCellValue(double.Parse(mk[m].Split('-')[h]));
                                cell.CellStyle = hsRight;
                            }
                            else
                            {
                                cell.SetCellValue(mk[m].Split('-')[h]);
                                cell.CellStyle = hsRight;
                            }
                        }
                        else
                        {
                            cell.SetCellValue(mk[m].Split('-')[h]);
                            cell.CellStyle = headerLabelCellStyle;
                        }
                        cell.Row.Height = 500;

                    }

                    cell = row.CreateCell(5);
                    cell.CellFormula = "H" + (rowIndex + 1).ToString() + "/(IF(D" + (rowIndex + 1).ToString() + "=0,1,D" + (rowIndex + 1).ToString() + "))";
                    cell.Row.Height = 500;
                    cell.CellStyle = hsRight;

                    cell = row.CreateCell(6);
                    cell.CellFormula = "I" + (rowIndex + 1).ToString() + "/(IF(E" + (rowIndex + 1).ToString() + "=0,1,E" + (rowIndex + 1).ToString() + "))";
                    cell.Row.Height = 500;
                    cell.CellStyle = hsRight;

                    cell = row.CreateCell(9);
                    cell.CellFormula = "Sum(H" + (rowIndex + 1).ToString() + ":I" + (rowIndex + 1).ToString() + ")";
                    cell.Row.Height = 500;
                    cell.CellStyle = hs4;


                }
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    sheet.AutoSizeColumn(j);
                }
            }



            using (var exportData = new MemoryStream())
            {
                workbook.Write(exportData);
                string filename = dlBenBan.SelectedIndex > 0 ? dlBenBan.SelectedItem.Text : "";
                string saveAsFileName = "BCNH-TongHop-" + filename + " " + ngaythang + ".xls";

                Response.ContentType = "application/vnd.ms-excel";
                Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", saveAsFileName));
                Response.Clear();
                Response.BinaryWrite(exportData.GetBuffer());
                Response.End();
            }

        }
    }

    void LoadLoaiVatLieu(DropDownList dl)
    {
        dl.Items.Clear();
        dl.Items.Insert(0, new ListItem("--Chọn vật liệu--", ""));
        dl.SelectedIndex = 0;
    }
    void LoadNhaCungCap(DropDownList dl, string title)
    {

        p = new SqlParameter[2];
        p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(txtTuNgay.Text));
        p[0].SqlDbType = SqlDbType.SmallDateTime;
        p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(txtDenNgay.Text));
        p[1].SqlDbType = SqlDbType.SmallDateTime;

        dl.Items.Clear();
        dl.DataSource = ex.GetData("sp_BCNH_LoadBenBanByTime", p);
        dl.DataBind();
        dl.Items.Insert(0, new ListItem(title, ""));
        dl.SelectedIndex = 0;
    }
    void LoadTramTron(DropDownList dl)
    {
        dlTramTron.Items.Clear();
        dlTramTron.DataSource = ex.GetData("sp_xdanhaphang_ListTramTron");
        dlTramTron.DataBind();
        dlTramTron.Items.Insert(0, new ListItem("--Chọn trạm trộn--", ""));
        dlTramTron.SelectedIndex = 0;
    }
    protected void dlThang_SelectedIndexChanged(object sender, EventArgs e)
    {
        if (dlThang.SelectedIndex.Equals(0))
        {
            txtTuNgay.Visible = true;
            txtDenNgay.Visible = true;
            dlNam.Visible = false;
            //lbTrangThai.Visible = false;
            //btnKhoa.Visible = false;
        }
        else
        {
            txtTuNgay.Visible = false;
            txtDenNgay.Visible = false;
            dlNam.Visible = true;
            //lbTrangThai.Visible = true;
            //btnKhoa.Visible = true;
        }

        getDate();

        p = new SqlParameter[2];
        p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
        p[0].SqlDbType = SqlDbType.SmallDateTime;
        p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
        p[1].SqlDbType = SqlDbType.SmallDateTime;

        dlBenBan.DataSource = ex.GetData("sp_BCNH_TongHop_LoadBenBanByTime", p);
        dlBenBan.DataBind();
        dlBenBan.Items.Insert(0, new ListItem("--Chọn bên bán--", ""));
        dlBenBan.SelectedIndex = 0;

        //if (dlThang.SelectedIndex > 0)
        //{
        //    //load trang thai khoa
        //    p = new SqlParameter[3];
        //    p[0] = new SqlParameter("@Name", "BCNH");
        //    p[0].SqlDbType = SqlDbType.NVarChar;
        //    p[1] = new SqlParameter("@Thang", int.Parse(dlThang.SelectedValue));
        //    p[1].SqlDbType = SqlDbType.Int;
        //    p[2] = new SqlParameter("@Nam", int.Parse(dlNam.SelectedValue));
        //    p[2].SqlDbType = SqlDbType.Int;

        //    string index = ex.GetValueString("sp_xdakhoa_Check", p);

        //    if (index.Trim().Equals("") || index.Trim().Equals("1"))
        //    {
        //        lbTrangThai.Text = "Trạng thái: Dữ liệu đang mở";
        //        btnKhoa.Text = "Khóa lại";
        //    }
        //    else
        //    {
        //        lbTrangThai.Text = "Trạng thái: Dữ liệu đang bị khóa";
        //        btnKhoa.Text = "Mở lại";
        //    }
        //}
    }
    public void getDate()
    {
        if (dlThang.SelectedIndex.Equals(0))
        {
            hdTuNgay.Value = txtTuNgay.Text;
            hdDenNgay.Value = txtDenNgay.Text;
        }
        else
        {
            txtTuNgay.Text = "1/" + dlThang.SelectedValue + "/" + dlNam.SelectedValue;
            txtDenNgay.Text = GetLastDayOfMonth(int.Parse(dlThang.SelectedValue)).ToString("dd/MM/yyyy");
            hdTuNgay.Value = txtTuNgay.Text;
            hdDenNgay.Value = txtDenNgay.Text;
        }
    }
    public DateTime GetLastDayOfMonth(int iMonth)
    {
        DateTime dtResult = new DateTime(int.Parse(dlNam.SelectedValue), iMonth, 1);
        dtResult = dtResult.AddMonths(1);
        dtResult = dtResult.AddDays(-(dtResult.Day));
        return dtResult;
    }
    protected void dlBenBan_SelectedIndexChanged(object sender, EventArgs e)
    {
        if (dlBenBan.SelectedIndex > 0)
        {
            p = new SqlParameter[3];
            p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
            p[0].SqlDbType = SqlDbType.SmallDateTime;
            p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
            p[1].SqlDbType = SqlDbType.SmallDateTime;
            p[2] = new SqlParameter("@BenBan", ex.GetGuid(dlBenBan.SelectedValue));
            p[2].SqlDbType = SqlDbType.UniqueIdentifier;

            dlLoaiVLLoc.DataSource = ex.GetData("sp_BCNH_LoadLoaiVLByBenBan", p);
            dlLoaiVLLoc.DataBind();
            dlLoaiVLLoc.Items.Insert(0, new ListItem("--Chọn vật liệu--", ""));
            dlLoaiVLLoc.SelectedIndex = 0;
        }
        else
        {
            LoadLoaiVatLieu(dlLoaiVLLoc);
        }
    }
    protected void btnXem_Click(object sender, EventArgs e)
    {
        if (f_SuDung.Equals(1))
        {

            CurrentPage = 1;
            btnNext.Enabled = true;
            if (dlBenBan.SelectedIndex.Equals(0) && dlLoaiVLLoc.SelectedIndex.Equals(0) && dlTramTron.SelectedIndex.Equals(0))
                SearchAll(1);
            else
                if (dlBenBan.SelectedIndex > 0 && dlLoaiVLLoc.SelectedIndex.Equals(0) && dlTramTron.SelectedIndex.Equals(0))
                    SearchByBenBan(1);
                else
                    if (dlBenBan.SelectedIndex > 0 && dlLoaiVLLoc.SelectedIndex > 0 && dlTramTron.SelectedIndex.Equals(0))
                        SearchByBenBanLoaiVL(1);
        }
        else
            gstGetMess("Bạn không có quyền xem báo cáo này", "Home.aspx");
    }
    private void SearchByBenBan(int page)
    {
        getDate();
        p = new SqlParameter[5];
        p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
        p[0].SqlDbType = SqlDbType.SmallDateTime;
        p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
        p[1].SqlDbType = SqlDbType.SmallDateTime;
        p[2] = new SqlParameter("@BenBan", ex.GetGuid(dlBenBan.SelectedValue));
        p[2].SqlDbType = SqlDbType.UniqueIdentifier;
        p[3] = new SqlParameter("@PageNumber", page);
        p[3].SqlDbType = SqlDbType.Int;
        p[4] = new SqlParameter("@RowspPage", PageNumber);
        p[4].SqlDbType = SqlDbType.Int;

        GV.DataSource = ex.GetData("sp_BCNH_TH_ByBenBan", p);
        GV.DataBind();

        p = new SqlParameter[3];
        p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
        p[0].SqlDbType = SqlDbType.SmallDateTime;
        p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
        p[1].SqlDbType = SqlDbType.SmallDateTime;
        p[2] = new SqlParameter("@BenBan", ex.GetGuid(dlBenBan.SelectedValue));
        p[2].SqlDbType = SqlDbType.UniqueIdentifier;

        dt = ex.GetData("sp_BCNH_TH_ByBenBan_GetFooter", p);
        LoadFooter();
    }
    private void SearchAll(int page)
    {
        getDate();
        p = new SqlParameter[4];
        p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
        p[0].SqlDbType = SqlDbType.SmallDateTime;
        p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
        p[1].SqlDbType = SqlDbType.SmallDateTime;

        p[2] = new SqlParameter("@PageNumber", page);
        p[2].SqlDbType = SqlDbType.Int;
        p[3] = new SqlParameter("@RowspPage", PageNumber);
        p[3].SqlDbType = SqlDbType.Int;

        GV.DataSource = ex.GetData("sp_BCNH_TH_TongHop", p);
        GV.DataBind();

        p = new SqlParameter[2];
        p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
        p[0].SqlDbType = SqlDbType.SmallDateTime;
        p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
        p[1].SqlDbType = SqlDbType.SmallDateTime;

        dt = ex.GetData("sp_BCNH_TH_TongHop_GetFooter", p);
        LoadFooter();
    }
    private void SearchByBenBanLoaiVL(int page)
    {
        getDate();
        p = new SqlParameter[6];
        p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
        p[0].SqlDbType = SqlDbType.SmallDateTime;
        p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
        p[1].SqlDbType = SqlDbType.SmallDateTime;
        p[2] = new SqlParameter("@BenBan", ex.GetGuid(dlBenBan.SelectedValue));
        p[2].SqlDbType = SqlDbType.UniqueIdentifier;
        p[3] = new SqlParameter("@LoaiVL", ex.GetGuid(dlLoaiVLLoc.SelectedValue));
        p[3].SqlDbType = SqlDbType.UniqueIdentifier;

        p[4] = new SqlParameter("@PageNumber", page);
        p[4].SqlDbType = SqlDbType.Int;
        p[5] = new SqlParameter("@RowspPage", PageNumber);
        p[5].SqlDbType = SqlDbType.Int;

        GV.DataSource = ex.GetData("sp_BCNH_TH_ByBenBanLoaiVL", p);
        GV.DataBind();

        p = new SqlParameter[4];
        p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
        p[0].SqlDbType = SqlDbType.SmallDateTime;
        p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
        p[1].SqlDbType = SqlDbType.SmallDateTime;
        p[2] = new SqlParameter("@LoaiVL", ex.GetGuid(dlLoaiVLLoc.SelectedValue));
        p[2].SqlDbType = SqlDbType.UniqueIdentifier;
        p[3] = new SqlParameter("@BenBan", ex.GetGuid(dlBenBan.SelectedValue));
        p[3].SqlDbType = SqlDbType.UniqueIdentifier;

        dt = ex.GetData("sp_BCNH_TH_ByBenBanLoaiVL_GetFooter", p);
        LoadFooter();
    }
    private void LoadFooter()
    {
        if (dt.Rows.Count > 0)
        {
            tlhang = double.Parse(dt.Rows[0]["TLHang"].ToString());
            klsauquidoi = double.Parse(dt.Rows[0]["KLSauQuiDoi"].ToString());
            klthucte = double.Parse(dt.Rows[0]["KLThucTe"].ToString());
            klmuaVAT = double.Parse(dt.Rows[0]["KLMuaVAT"].ToString());
            klmuaKVAT = double.Parse(dt.Rows[0]["KLMuaKoVAT"].ToString());
            //giavlvat = double.Parse(dt.Rows[0]["GiaVLCoVAT"].ToString());
            //giavlkhongvat = double.Parse(dt.Rows[0]["GiaVLKhongVAT"].ToString());
            ttvlcovat = double.Parse(dt.Rows[0]["ThanhTienVLCoVAT"].ToString());
            ttvlkvat = double.Parse(dt.Rows[0]["ThanhTienVLKhongVAT"].ToString());
            //cvccovat = double.Parse(dt.Rows[0]["GiaCuocCoVAT"].ToString());
            //cvckvt = double.Parse(dt.Rows[0]["GiaCuocKhongVAT"].ToString());

            ttcvccovat = double.Parse(dt.Rows[0]["ThanhTienCuocVCCoVAT"].ToString());
            ttcvckvat = double.Parse(dt.Rows[0]["ThanhTienCuocVCKhongVAT"].ToString());
            tt1m3covat = double.Parse(dt.Rows[0]["ThanhTien1m3CoVAT"].ToString());
            tt1m3kvat = double.Parse(dt.Rows[0]["ThanhTien1m3KhongVAT"].ToString());
            tt1chuyencovat = double.Parse(dt.Rows[0]["ThanhTien1ChuyenCoVAT"].ToString());
            tt1chuyenkvat = double.Parse(dt.Rows[0]["ThanhTien1ChuyenKhongVAT"].ToString());

            //if (dlLoaiVLLoc.SelectedIndex > 0)
            //{
            //    p = new SqlParameter[3];
            //    p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
            //    p[0].SqlDbType = SqlDbType.SmallDateTime;
            //    p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
            //    p[1].SqlDbType = SqlDbType.SmallDateTime;
            //    p[2] = new SqlParameter("@LoaiVL", ex.GetGuid(dlLoaiVLLoc.SelectedValue));
            //    p[2].SqlDbType = SqlDbType.UniqueIdentifier;

            //    dt = ex.GetData("sp_BCNH_LoaiVL_ByLoaiVL_GiaTB", p);
            //    if (dt.Rows.Count > 0)
            //    {
            //        giavlvat = double.Parse(dt.Rows[0][0].ToString());
            //        giavlkhongvat = double.Parse(dt.Rows[0][1].ToString());

            //        cvccovat = double.Parse(dt.Rows[0][2].ToString());
            //        cvckvt = double.Parse(dt.Rows[0][3].ToString());
            //    }
            //}
        }
        if (GV.Rows.Count > 0)
        {
            GV.FooterRow.Cells[3].Text = "Tổng cộng";
            GV.FooterRow.Cells[3].HorizontalAlign = HorizontalAlign.Center;
            GV.FooterRow.Cells[6].Text = ConvertDecimal(klmuaVAT);
            GV.FooterRow.Cells[7].Text = ConvertDecimal(klmuaKVAT);
            GV.FooterRow.Cells[8].Text = ConvertDecimal(klthucte);
            GV.FooterRow.Cells[9].Text = ConvertDecimal(tlhang);
            GV.FooterRow.Cells[10].Text = ConvertDecimal(klsauquidoi);
            //GV.FooterRow.Cells[11].Text = ConvertDecimal(giavlvat);
            //GV.FooterRow.Cells[12].Text = ConvertDecimal(giavlkhongvat);
            GV.FooterRow.Cells[13].Text = ConvertDecimal(ttvlcovat);
            GV.FooterRow.Cells[14].Text = ConvertDecimal(ttvlkvat);
            //GV.FooterRow.Cells[15].Text = ConvertDecimal(cvccovat);
            //GV.FooterRow.Cells[16].Text = ConvertDecimal(cvckvt);
            GV.FooterRow.Cells[17].Text = ConvertDecimal(ttcvccovat);
            GV.FooterRow.Cells[18].Text = ConvertDecimal(ttcvckvat);

            GV.FooterRow.Cells[19].Text = ConvertDecimal(tt1m3covat);
            GV.FooterRow.Cells[20].Text = ConvertDecimal(tt1m3kvat);
            GV.FooterRow.Cells[21].Text = ConvertDecimal(tt1chuyencovat);
            GV.FooterRow.Cells[22].Text = ConvertDecimal(tt1chuyenkvat);
        }
    }
    protected void btnPre_Click(object sender, EventArgs e)
    {
        if (CurrentPage > 1)
        {
            CurrentPage--;
            btnPre.Enabled = true;
            btnNext.Enabled = true;
            btnFirst.Enabled = true;

            if (dlBenBan.SelectedIndex.Equals(0) && dlLoaiVLLoc.SelectedIndex.Equals(0) && dlTramTron.SelectedIndex.Equals(0))
                SearchAll(CurrentPage);
            else
                if (dlBenBan.SelectedIndex > 0 && dlLoaiVLLoc.SelectedIndex.Equals(0) && dlTramTron.SelectedIndex.Equals(0))
                    SearchByBenBan(CurrentPage);
                else
                    if (dlBenBan.SelectedIndex > 0 && dlLoaiVLLoc.SelectedIndex > 0 && dlTramTron.SelectedIndex.Equals(0))
                        SearchByBenBanLoaiVL(CurrentPage);
        }
        else
        {
            btnFirst.Enabled = false;
            btnPre.Enabled = false;
            btnNext.Enabled = true;
        }

    }
    protected void btnNext_Click(object sender, EventArgs e)
    {
        CurrentPage++;

        if (dlBenBan.SelectedIndex.Equals(0) && dlLoaiVLLoc.SelectedIndex.Equals(0) && dlTramTron.SelectedIndex.Equals(0))
            SearchAll(CurrentPage);
        else
            if (dlBenBan.SelectedIndex > 0 && dlLoaiVLLoc.SelectedIndex.Equals(0) && dlTramTron.SelectedIndex.Equals(0))
                SearchByBenBan(CurrentPage);
            else
                if (dlBenBan.SelectedIndex > 0 && dlLoaiVLLoc.SelectedIndex > 0 && dlTramTron.SelectedIndex.Equals(0))
                    SearchByBenBanLoaiVL(CurrentPage);


        if (GV.Rows.Count.Equals(0))
        {
            btnPre.Enabled = true;
            btnNext.Enabled = false;
        }
        else
        {
            btnPre.Enabled = true;
            btnNext.Enabled = true;
        }
        btnFirst.Enabled = true;

    }
    protected void btnFirst_Click(object sender, EventArgs e)
    {
        CurrentPage = 1;

        if (dlBenBan.SelectedIndex.Equals(0) && dlLoaiVLLoc.SelectedIndex.Equals(0) && dlTramTron.SelectedIndex.Equals(0))
            SearchAll(CurrentPage);
        else
            if (dlBenBan.SelectedIndex > 0 && dlLoaiVLLoc.SelectedIndex.Equals(0) && dlTramTron.SelectedIndex.Equals(0))
                SearchByBenBan(CurrentPage);
            else
                if (dlBenBan.SelectedIndex > 0 && dlLoaiVLLoc.SelectedIndex > 0 && dlTramTron.SelectedIndex.Equals(0))
                    SearchByBenBanLoaiVL(CurrentPage);

        btnPre.Enabled = false;
        btnNext.Enabled = true;
        btnFirst.Enabled = false;

    }
    string ConvertDecimal(double s)
    {
        string q = "";
        if (s.ToString().Contains("."))
        {
            double x = double.Parse(s.ToString().Substring(0, s.ToString().IndexOf('.')));
            q = (x != 0 && x > 999) ? string.Format("{0:0,0}", x) : x.ToString();
            q += s.ToString().Substring(s.ToString().IndexOf('.'));
        }
        else
            q = (s != 0 && s > 999) ? string.Format("{0:0,0}", s) : s.ToString();
        return q;
    }
    protected void dlLoaiBaoCao_SelectedIndexChanged(object sender, EventArgs e)
    {
        if (dlLoaiBaoCao.SelectedIndex.Equals(0))
            Response.Redirect("BCNHTongHop.aspx");
        else if (dlLoaiBaoCao.SelectedIndex.Equals(1))
            Response.Redirect("BCNHBenChayCuoc.aspx");
        else if (dlLoaiBaoCao.SelectedIndex.Equals(2))
            Response.Redirect("BCNHBenBan.aspx");
        else
            if (dlLoaiBaoCao.SelectedIndex.Equals(3))
                Response.Redirect("BCNHBienSoXe.aspx");
            else if (dlLoaiBaoCao.SelectedIndex.Equals(4))
                Response.Redirect("BCNHLoaiVL.aspx");
    }
   
    private void gstGetMess(string gstMess, string gstLink)
    {
        if (gstLink == "")
            ScriptManager.RegisterClientScriptBlock(this, this.GetType(), " ", "alert('" + gstMess + "')", true);
        else
            ScriptManager.RegisterClientScriptBlock(this, this.GetType(), " ", "alert('" + gstMess + "');window.location.href='" + gstLink + "'", true);

    }
}